package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.spc_chart_report.SpcChartReportBean;
import com.navi.bean.out.spc_chart_report.table.*;
import com.navi.bean.sql.SqlVo;
import com.navi.core.context.HistoryContext;
import com.navi.core.mysqlEntity.SpcRetChartDataEntity;
import com.navi.core.repository.NaviRepository;
import com.navi.core.util.JacksonUtil;
import com.navi.core.util.JdbcUtils;
import com.navi.core.util.LoggerUtil;
import com.navi.mould1.service.ISpcChartReportService;
import com.navi.mould1.tx.xpspcchart.XpspcchartI;
import com.navi.mould1.tx.xpspcchart.XpspcchartO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;
import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.constant.GenericDef.RETURN_CODE_OK;
import static com.navi.core.constant.GenericDef.RETURN_MESG_OK;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_Update;
import static java.util.Comparator.comparing;
import static java.util.stream.Collectors.maxBy;


@Slf4j
@Service
@Transactional
public class SpcChartReportServiceImpl implements ISpcChartReportService {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    QueryRunner qr = new QueryRunner();

    @Autowired
    private NaviRepository dao;


    @Override
    public String subMainProc(String strInObj) {
        HistoryContext.clear(this.getClass());

        XpspcchartI inTrx = new XpspcchartI();
        XpspcchartO outTrx = new XpspcchartO();

        inTrx = JacksonUtil.fromJson(strInObj, XpspcchartI.class);
        log.info(strInObj);

        String actionFlg = inTrx.getAction_flg();
        switch (actionFlg) {
            case "A" :
                outTrx = addComment(inTrx);
                break;
            case "H" :
                outTrx = hidePoint(inTrx);
                break;
            default:
                break;
        }

        final String strOutTrx = JacksonUtil.toJSONStr(outTrx);
        return strOutTrx;
    }


    public SpcChartReportBean getGridData(ConditionBean conditions) {
        SpcChartReportBean spcChartReportBean = new SpcChartReportBean();

        /*** 控制线，中心线等数据查询 **/
        final SqlVo sqlVo1 = new SqlVo();
        sqlVo1.appendSql("SELECT");
        sqlVo1.appendSql("A.col_typ_fk, A.grp_no_fk, A.chart_no_fk, A.chart_typ_fk, b_2.chart_name, A.chart_usl, A.chart_ucl, A.chart_target, A.chart_lcl, A.chart_lsl, A.chart_lpl, A.chart_mean, A.chart_spec_chk_flg, A.chart_upl");
        sqlVo1.appendSql("FROM icim_jn.spc_ret_chart_info A");
        sqlVo1.appendSql("INNER JOIN (select col_typ_fk, grp_no_fk, chart_no, chart_name FROM icim_jn.spc_bis_chart_main) b_2 ON A.col_typ_fk = b_2.col_typ_fk AND A.grp_no_fk = b_2.grp_no_fk AND A.chart_no_fk = b_2.chart_no");
        sqlVo1.appendSql("WHERE 1=1");
        if (StringUtils.isNotEmpty(conditions.getControl_chart_grp())) {
            String grpNo_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String colType_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo1.appendSql("AND A.grp_no_fk in" + grpNo_list_str);
            sqlVo1.appendSql("AND A.col_typ_fk in" + colType_list_str);
        }
        if (StringUtils.isNotEmpty(conditions.getControl_chart())) {
            String chartNo_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartTyp_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartName_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[2]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo1.appendSql("AND A.chart_no_fk in" + chartNo_list_str);
            sqlVo1.appendSql("AND A.chart_typ_fk in" + chartTyp_list_str);
            sqlVo1.appendSql("AND b_2.chart_name in" + chartName_list_str);
        }

        List<Spc_ret_chart_info> resultDataList_1 = mySql_QueryRunner_QueryListBySql(sqlVo1, Spc_ret_chart_info.class);

        if (CollectionUtils.isNotEmpty(resultDataList_1)) {
            Spc_ret_chart_info Spc_ret_chart_info  = resultDataList_1.stream().collect(maxBy(comparing(data -> data.getChart_ucl()))).orElseThrow(RuntimeException::new);
            spcChartReportBean.setMainChartSetting(Spc_ret_chart_info);
        }




        /*** 查询grid/chart图点值 **/
        final SqlVo sqlVo2 = new SqlVo();
        sqlVo2.appendSql("SELECT");
        sqlVo2.appendSql("b_1.tool_dsc AS tool_id,");
        sqlVo2.appendSql("b_2.chart_name,");
        sqlVo2.appendSql("B.*");
        sqlVo2.appendSql("FROM spc_data.spc_ret_chart_data B");
        sqlVo2.appendSql("INNER JOIN (select col_typ_fk,grp_no_fk,chart_no,chart_name from icim_jn.spc_bis_chart_main ) b_2 ON B.col_typ_fk = b_2.col_typ_fk and B.grp_no_fk = b_2.grp_no_fk and B.chart_no_fk = b_2.chart_no");
        sqlVo2.appendSql("LEFT JOIN (select tool_id,tool_dsc from icim_jn.bis_tool ) b_1 ON B.m_tool_id_fk = b_1.tool_id");
        sqlVo2.appendSql("WHERE 1=1");
        if (StringUtils.isNotEmpty(conditions.getControl_chart_grp())) {
            String grpNo_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String colType_list_str = Arrays.stream(conditions.getControl_chart_grp().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo2.appendSql("AND B.grp_no_fk in" + grpNo_list_str);
            sqlVo2.appendSql("AND B.col_typ_fk in" + colType_list_str);
        }
        if (StringUtils.isNotEmpty(conditions.getControl_chart())) {
            String chartNo_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[0]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartTyp_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[1]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            String chartName_list_str = Arrays.stream(conditions.getControl_chart().split(",")).map(data -> "'"+data.split("@")[2]+"'").distinct().collect(Collectors.joining(",", "(", ")"));
            sqlVo2.appendSql("AND B.chart_no_fk in" + chartNo_list_str);
            sqlVo2.appendSql("AND B.chart_typ_fk in" + chartTyp_list_str);
            sqlVo2.appendSql("AND b_2.chart_name in" + chartName_list_str);
        }
        if ("N".equals(conditions.getShow_hide_point())) sqlVo2.appendSql("AND B.exd_flg <> 'N'"); //是否显示隐藏点
        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo2.appendSql("AND B.fab_id_fk in" + jointInSql(conditions.getFab_id()));
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo2.appendSql("AND B.line_id_fk in" + jointInSql(conditions.getBay_id()));
        if (StringUtils.isNotEmpty(conditions.getOpe_id())) sqlVo2.appendSql("AND B.p_ope_id_fk in" + jointInSql(conditions.getOpe_id()));
        if (StringUtils.isNotEmpty(conditions.getTool_main_id())) sqlVo2.appendSql("AND B.p_tool_id_fk in(select tool_id from bis_tool where root_tool_id in" + jointInSql(conditions.getTool_main_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getTool_sub_id())) sqlVo2.appendSql("AND B.p_tool_id_fk in" + jointInSql(conditions.getTool_sub_id()));
        if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo2.appendSql("AND B.p_tool_id_fk in" + jointInSql(conditions.getTool_id()));
        if (StringUtils.isNotEmpty(conditions.getShift_id())) sqlVo2.appendSql("AND B.shift_id_fk in" + jointInSql(conditions.getShift_id()));
        if (StringUtils.isNotEmpty(conditions.getBoat_number())) sqlVo2.appendSql("AND B.boat_number in" + jointInSql(conditions.getBoat_number()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo2.appendSqlAndAddParam("AND B.input_timestamp_2 >= ? ", conditions.getStart_time());
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo2.appendSqlAndAddParam("AND B.input_timestamp_2 <= ? ", conditions.getEnd_time());

        List<Spc_ret_chart_data> resultDataList_2 = mySql_QueryRunner_QueryListBySql(sqlVo2, Spc_ret_chart_data.class);

        spcChartReportBean.setOary(resultDataList_2);
        spcChartReportBean.setOary2(madeChartRawOut2(resultDataList_2));
        spcChartReportBean.setChartGridVo_list(getChartGridVo_list(resultDataList_1, resultDataList_2));
        //汇总数据的统计
        List<ChartDataAndRawDataPo> totalPointData = new ArrayList<>();
        spcChartReportBean.getChartGridVo_list().forEach(gridVo ->{ totalPointData.addAll(gridVo.getPointData());});
        spcChartReportBean.setTotalPointData(totalPointData);
        return spcChartReportBean;
    }




    public List<SpcChartGridDataVo> getChartGridVo_list(List<Spc_ret_chart_info> spcRetChartInfoList, List<Spc_ret_chart_data> spcRetChartDataList){
        List<SpcChartGridDataVo> chartGridDataVO_list = new ArrayList<>();
        long startTime = System.currentTimeMillis();
        for (Spc_ret_chart_info spc_ret_chart_info : spcRetChartInfoList) {
            SpcChartGridDataVo spcChartGridDataVO = new SpcChartGridDataVo();
            Spc_ret_chart_info spc_ret_chart_info_copy = new Spc_ret_chart_info();
            org.springframework.beans.BeanUtils.copyProperties(spc_ret_chart_info, spc_ret_chart_info_copy);
            spcChartGridDataVO.setChartSetting(spc_ret_chart_info_copy);
            spcChartGridDataVO.setPointData(
                    madeChartRawOut2(
                            spcRetChartDataList.stream().filter(spc_ret_chart_data ->
                                    spc_ret_chart_info.getCol_typ_fk().equals(spc_ret_chart_data.getCol_typ_fk())
                                            && spc_ret_chart_info.getGrp_no_fk().equals(spc_ret_chart_data.getGrp_no_fk())
                                            && spc_ret_chart_info.getChart_no_fk().equals(spc_ret_chart_data.getChart_no_fk())
                                            && spc_ret_chart_info.getChart_typ_fk().equals(spc_ret_chart_data.getChart_typ_fk()))
                                    .collect(Collectors.toList())
                    )
            );
            chartGridDataVO_list.add(spcChartGridDataVO);
        }
        long endTime = System.currentTimeMillis();
        logger.info("查询Spc_ret_chart_raw总共用时:" + (endTime - startTime));
        return chartGridDataVO_list;
    }


    /*** 查询子管制图原始数据 **/
    public List<ChartDataAndRawDataPo> madeChartRawOut(List<Spc_ret_chart_data> spcRetChartDataList){
        List<Spc_ret_chart_raw> spcRetChartRawList = new LinkedList<Spc_ret_chart_raw>();
        List<ChartDataAndRawDataPo>  newChartRaws = new LinkedList<ChartDataAndRawDataPo>();
        if(spcRetChartDataList.size() > 0){
            long startTime = System.currentTimeMillis();
            for(int i = 0; i < spcRetChartDataList.size() ; i++){
                ChartDataAndRawDataPo newChartRaw = new ChartDataAndRawDataPo();
                //find raw data
                String colTyp = spcRetChartDataList.get(i).getCol_typ_fk();
                String grpNo = spcRetChartDataList.get(i).getGrp_no_fk();
                String chartNo = spcRetChartDataList.get(i).getChart_no_fk();
                String subGrpNoFk = spcRetChartDataList.get(i).getSub_grp_no_fk();
                Long InputTimeD = spcRetChartDataList.get(i).getInput_time_d();
                SqlVo sqlVo = new SqlVo("select * from icim_jn.spc_ret_chart_raw where 1=1");
                if(StringUtils.isNotEmpty(colTyp)) sqlVo.appendSql("and col_typ_fk = '" + colTyp + "'");
                if(StringUtils.isNotEmpty(grpNo)) sqlVo.appendSql("and grp_no_fk = '" + grpNo + "'");
                if(StringUtils.isNotEmpty(chartNo)) sqlVo.appendSql("and chart_no_fk = '" + chartNo + "'");
                if(StringUtils.isNotEmpty(subGrpNoFk)) sqlVo.appendSql("and sub_grp_no = '" + subGrpNoFk + "'");
                sqlVo.appendSql("and proc_time_d = '" + InputTimeD + "'");
                sqlVo.appendSql("order by proc_time_d asc");
                try (Connection conn = JdbcUtils.getMySQLConnection()){
                    spcRetChartRawList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(Spc_ret_chart_raw.class));
                } catch (Exception e) {
                    LoggerFactory.getLogger(this.getClass()).error(LoggerUtil.stackTraceToString(e));
                    e.printStackTrace();
                }
                newChartRaw.setCol_typ_fk(spcRetChartDataList.get(i).getCol_typ_fk());
                newChartRaw.setGrp_no_fk(spcRetChartDataList.get(i).getGrp_no_fk());
                newChartRaw.setChart_no_fk(spcRetChartDataList.get(i).getChart_no_fk());
                newChartRaw.setChart_typ_fk(spcRetChartDataList.get(i).getCol_typ_fk());
                newChartRaw.setInput_time_d(spcRetChartDataList.get(i).getInput_time_d());
                newChartRaw.setUnq_seq_id(spcRetChartDataList.get(i).getUnq_seq_id());
                if(spcRetChartDataList.get(i).getInput_timestamp() != null){
                    newChartRaw.setInput_timestamp(spcRetChartDataList.get(i).getInput_timestamp().toString());
                }
                if(spcRetChartDataList.get(i).getProc_timestamp() != null){
                    newChartRaw.setProc_timestamp(spcRetChartDataList.get(i).getProc_timestamp().toString());
                }
                newChartRaw.setSub_grp_no_fk(spcRetChartDataList.get(i).getSub_grp_no_fk());
                newChartRaw.setEvt_usr(spcRetChartDataList.get(i).getEvt_usr());
                newChartRaw.setPrd_seq_id_fk(spcRetChartDataList.get(i).getPrd_seq_id_fk());
                newChartRaw.setExd_flg(spcRetChartDataList.get(i).getExd_flg());
                newChartRaw.setChart_value(spcRetChartDataList.get(i).getChart_value());
                newChartRaw.setSample_size_fk(spcRetChartDataList.get(i).getSample_size_fk());
                newChartRaw.setChart_spec_rlt_fk(spcRetChartDataList.get(i).getChart_spec_rlt_fk());
                newChartRaw.setChart_rule_1_rlt_fk(spcRetChartDataList.get(i).getChart_rule_1_rlt_fk());
                newChartRaw.setChart_rule_2_rlt_fk(spcRetChartDataList.get(i).getChart_rule_2_rlt_fk());
                newChartRaw.setOoc_ocap_fk(spcRetChartDataList.get(i).getOoc_ocap_fk());
                newChartRaw.setOos_ocap_fk(spcRetChartDataList.get(i).getOos_ocap_fk());
                newChartRaw.setChart_total_action_fk(spcRetChartDataList.get(i).getChart_total_action_fk());
                newChartRaw.setChart_detail_action_fk(spcRetChartDataList.get(i).getChart_detail_action_fk());
                newChartRaw.setChart_usl_fk(spcRetChartDataList.get(i).getChart_usl_fk());
                newChartRaw.setChart_ucl_fk(spcRetChartDataList.get(i).getChart_ucl_fk());
                newChartRaw.setChart_uwl_fk(spcRetChartDataList.get(i).getChart_uwl_fk());
                newChartRaw.setChart_mean_fk(spcRetChartDataList.get(i).getChart_mean_fk());
                newChartRaw.setChart_lwl_fk(spcRetChartDataList.get(i).getChart_lwl_fk());
                newChartRaw.setChart_lcl_fk(spcRetChartDataList.get(i).getChart_lcl_fk());
                newChartRaw.setChart_lsl_fk(spcRetChartDataList.get(i).getChart_lsl_fk());
                newChartRaw.setMdl_id_fk(spcRetChartDataList.get(i).getMdl_id_fk());
                newChartRaw.setP_tool_id_fk(spcRetChartDataList.get(i).getP_tool_id_fk());
                newChartRaw.setP_recipe_id_fk(spcRetChartDataList.get(i).getP_recipe_id_fk());
                newChartRaw.setP_path_id_fk(spcRetChartDataList.get(i).getP_path_id_fk());
                newChartRaw.setP_ope_no_fk(spcRetChartDataList.get(i).getP_ope_no_fk());
                newChartRaw.setP_ope_id_fk(spcRetChartDataList.get(i).getP_ope_id_fk());
                newChartRaw.setP_proc_id(spcRetChartDataList.get(i).getP_proc_id());
                newChartRaw.setM_tool_id_fk(spcRetChartDataList.get(i).getM_tool_id_fk());
                newChartRaw.setM_recipe_id_fk(spcRetChartDataList.get(i).getM_recipe_id_fk());
                newChartRaw.setChart_comment_rsn(spcRetChartDataList.get(i).getChart_comment_rsn());
                newChartRaw.setChart_comment(spcRetChartDataList.get(i).getChart_comment());
                newChartRaw.setChart_comment_usr(spcRetChartDataList.get(i).getChart_comment_usr());
                if(spcRetChartDataList.get(i).getChart_comment_timestamp() !=null){
                    newChartRaw.setChart_comment_timestamp(spcRetChartDataList.get(i).getChart_comment_timestamp().toString());
                }
                newChartRaw.setWo_id_fk(spcRetChartDataList.get(i).getWo_id_fk());
                newChartRaw.setSo_id_fk(spcRetChartDataList.get(i).getSo_id_fk());
                newChartRaw.setLot_id_fk(spcRetChartDataList.get(i).getLot_id_fk());
                newChartRaw.setCus_id_fk(spcRetChartDataList.get(i).getCus_id_fk());
                newChartRaw.setTool_id(spcRetChartDataList.get(i).getTool_id());
                newChartRaw.setRawDataList(spcRetChartRawList);
                newChartRaws.add(newChartRaw);
            }
            long endTime = System.currentTimeMillis();
            //logger.info("查询Spc_ret_chart_raw时间:" + (endTime-startTime));
        }
        return newChartRaws;
    }


    /*** 查询子管制图原始数据（优化查询版） **/
    public List<ChartDataAndRawDataPo> madeChartRawOut2(List<Spc_ret_chart_data> spcRetChartDataList){
        List<ChartDataAndRawDataPo>  newChartRaws = new LinkedList<ChartDataAndRawDataPo>();
        if(spcRetChartDataList.size() > 0){
            long startTime = System.currentTimeMillis();

            String colTyp = spcRetChartDataList.stream().filter(data -> StringUtils.isNotEmpty(data.getCol_typ_fk())).map(data -> data.getCol_typ_fk()).collect(Collectors.joining(","));
            String grpNo = spcRetChartDataList.stream().filter(data -> StringUtils.isNotEmpty(data.getGrp_no_fk())).map(data -> data.getGrp_no_fk()).collect(Collectors.joining(","));
            String chartNo = spcRetChartDataList.stream().filter(data -> StringUtils.isNotEmpty(data.getChart_no_fk())).map(data -> data.getChart_no_fk()).collect(Collectors.joining(","));
            String subGrpNoFk = spcRetChartDataList.stream().filter(data -> StringUtils.isNotEmpty(data.getSub_grp_no_fk())).map(data -> data.getSub_grp_no_fk()).collect(Collectors.joining(","));
            String InputTimeD = spcRetChartDataList.stream().filter(data -> data.getInput_time_d() != null).map(data -> String.valueOf(data.getInput_time_d())).collect(Collectors.joining(","));

            SqlVo sqlVo = new SqlVo("select * from icim_jn.spc_ret_chart_raw where 1=1");
            if(StringUtils.isNotEmpty(colTyp)) sqlVo.appendSql("and col_typ_fk in" + jointInSql(colTyp));
            if(StringUtils.isNotEmpty(grpNo)) sqlVo.appendSql("and grp_no_fk in" + jointInSql(grpNo));
            if(StringUtils.isNotEmpty(chartNo)) sqlVo.appendSql("and chart_no_fk in" + jointInSql(chartNo));
            if(StringUtils.isNotEmpty(subGrpNoFk)) sqlVo.appendSql("and sub_grp_no in" + jointInSql(subGrpNoFk));
            if(StringUtils.isNotEmpty(InputTimeD)) sqlVo.appendSql("and proc_time_d in" + jointInSql(InputTimeD));
            sqlVo.appendSql("order by proc_time_d asc");
            List<Spc_ret_chart_raw> spcRetChartRawTotalList = new LinkedList<Spc_ret_chart_raw>();
            try (Connection conn = JdbcUtils.getMySQLConnection()){
                spcRetChartRawTotalList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(Spc_ret_chart_raw.class));
            } catch (Exception e) {
                LoggerFactory.getLogger(this.getClass()).error(LoggerUtil.stackTraceToString(e));
                e.printStackTrace();
            }

            for(int i = 0; i < spcRetChartDataList.size() ; i++){
                Spc_ret_chart_data spcRetChartData = spcRetChartDataList.get(i);
                List<Spc_ret_chart_raw> spcRetChartRawList = spcRetChartRawTotalList.parallelStream().filter(data ->
                    (StringUtils.isNotEmpty(data.getCol_typ_fk()) && data.getCol_typ_fk().equals(spcRetChartData.getCol_typ_fk())) &&
                            (StringUtils.isNotEmpty(data.getGrp_no_fk()) && data.getGrp_no_fk().equals(spcRetChartData.getGrp_no_fk())) &&
                            (StringUtils.isNotEmpty(data.getChart_no_fk()) && data.getChart_no_fk().equals(spcRetChartData.getChart_no_fk())) &&
                            (StringUtils.isNotEmpty(data.getSub_grp_no()) && data.getSub_grp_no().equals(spcRetChartData.getSub_grp_no_fk())) &&
                            (data.getProc_time_d() != null) && String.valueOf(data.getProc_time_d()).equals(spcRetChartData.getInput_time_d())
                ).collect(Collectors.toList());

                ChartDataAndRawDataPo newChartRaw = new ChartDataAndRawDataPo();
                newChartRaw.setCol_typ_fk(spcRetChartData.getCol_typ_fk());
                newChartRaw.setGrp_no_fk(spcRetChartData.getGrp_no_fk());
                newChartRaw.setChart_no_fk(spcRetChartData.getChart_no_fk());
                newChartRaw.setChart_typ_fk(spcRetChartData.getCol_typ_fk());
                newChartRaw.setInput_time_d(spcRetChartData.getInput_time_d());
                newChartRaw.setUnq_seq_id(spcRetChartData.getUnq_seq_id());
                if(spcRetChartData.getInput_timestamp() != null) newChartRaw.setInput_timestamp(spcRetChartData.getInput_timestamp().toString());
                if(spcRetChartData.getProc_timestamp() != null) newChartRaw.setProc_timestamp(spcRetChartData.getProc_timestamp().toString());
                newChartRaw.setSub_grp_no_fk(spcRetChartData.getSub_grp_no_fk());
                newChartRaw.setEvt_usr(spcRetChartData.getEvt_usr());
                newChartRaw.setPrd_seq_id_fk(spcRetChartData.getPrd_seq_id_fk());
                newChartRaw.setExd_flg(spcRetChartData.getExd_flg());
                newChartRaw.setChart_value(spcRetChartData.getChart_value());
                newChartRaw.setSample_size_fk(spcRetChartData.getSample_size_fk());
                newChartRaw.setChart_spec_rlt_fk(spcRetChartData.getChart_spec_rlt_fk());
                newChartRaw.setChart_rule_1_rlt_fk(spcRetChartData.getChart_rule_1_rlt_fk());
                newChartRaw.setChart_rule_2_rlt_fk(spcRetChartData.getChart_rule_2_rlt_fk());
                newChartRaw.setOoc_ocap_fk(spcRetChartData.getOoc_ocap_fk());
                newChartRaw.setOos_ocap_fk(spcRetChartData.getOos_ocap_fk());
                newChartRaw.setChart_total_action_fk(spcRetChartData.getChart_total_action_fk());
                newChartRaw.setChart_detail_action_fk(spcRetChartData.getChart_detail_action_fk());
                newChartRaw.setChart_usl_fk(spcRetChartData.getChart_usl_fk());
                newChartRaw.setChart_ucl_fk(spcRetChartData.getChart_ucl_fk());
                newChartRaw.setChart_uwl_fk(spcRetChartData.getChart_uwl_fk());
                newChartRaw.setChart_mean_fk(spcRetChartData.getChart_mean_fk());
                newChartRaw.setChart_lwl_fk(spcRetChartData.getChart_lwl_fk());
                newChartRaw.setChart_lcl_fk(spcRetChartData.getChart_lcl_fk());
                newChartRaw.setChart_lsl_fk(spcRetChartData.getChart_lsl_fk());
                newChartRaw.setMdl_id_fk(spcRetChartData.getMdl_id_fk());
                newChartRaw.setP_tool_id_fk(spcRetChartData.getP_tool_id_fk());
                newChartRaw.setP_recipe_id_fk(spcRetChartData.getP_recipe_id_fk());
                newChartRaw.setP_path_id_fk(spcRetChartData.getP_path_id_fk());
                newChartRaw.setP_ope_no_fk(spcRetChartData.getP_ope_no_fk());
                newChartRaw.setP_ope_id_fk(spcRetChartData.getP_ope_id_fk());
                newChartRaw.setP_proc_id(spcRetChartData.getP_proc_id());
                newChartRaw.setM_tool_id_fk(spcRetChartData.getM_tool_id_fk());
                newChartRaw.setM_recipe_id_fk(spcRetChartData.getM_recipe_id_fk());
                newChartRaw.setChart_comment_rsn(spcRetChartData.getChart_comment_rsn());
                newChartRaw.setChart_comment(spcRetChartData.getChart_comment());
                newChartRaw.setChart_comment_usr(spcRetChartData.getChart_comment_usr());
                if(spcRetChartData.getChart_comment_timestamp() != null) newChartRaw.setChart_comment_timestamp(spcRetChartData.getChart_comment_timestamp().toString());
                newChartRaw.setWo_id_fk(spcRetChartData.getWo_id_fk());
                newChartRaw.setSo_id_fk(spcRetChartData.getSo_id_fk());
                newChartRaw.setLot_id_fk(spcRetChartData.getLot_id_fk());
                newChartRaw.setCus_id_fk(spcRetChartData.getCus_id_fk());
                newChartRaw.setTool_id(spcRetChartData.getTool_id());
                newChartRaw.setRawDataList(spcRetChartRawList);
                newChartRaws.add(newChartRaw);
            }
            long endTime = System.currentTimeMillis();
            //logger.info("查询Spc_ret_chart_raw时间:" + (endTime-startTime));
        }
        return newChartRaws;
    }


    /** 增加Chart图点值备注 ***/
    @Transactional
    public XpspcchartO addComment(XpspcchartI inTrx) {
        XpspcchartO outTrx = new XpspcchartO();
        String colTyp = inTrx.getCol_typ_fk();
        String grpNo = inTrx.getGrp_no_fk();
        String chartNo = inTrx.getChart_no_fk();
        String chartTyp = inTrx.getChart_typ_fk();
        String inputTime = inTrx.getInput_time_d();
        String comment = inTrx.getChart_comment();
        String evtUsr = inTrx.getEvt_usr();

        StringBuffer hql = new StringBuffer("From SpcRetChartDataEntity Where colTypFk = '" + colTyp +"'");
        hql.append(" and grpNoFk = '" + grpNo + "' and chartNoFk = '" + chartNo +"'");
        hql.append(" and chartTypFk = '" + chartTyp + "' and inputTimeD = '" + inputTime +"'");

        List<SpcRetChartDataEntity> spcRetChartDataEntityList = dao.find(SpcRetChartDataEntity.class, hql.toString());

        if (CollectionUtils.isEmpty(spcRetChartDataEntityList)) throw new RuntimeException("找不到SPC中对应的点，错误的输入");
        try {
            for (SpcRetChartDataEntity spcRetChartDataEntity : spcRetChartDataEntityList) {
                //hql方式
                //spcRetChartDataEntity.setChartComment(comment);
                //dao.update(spcRetChartDataEntity);

                //sql方式
                SqlVo sqlVo = new SqlVo("UPDATE spc_data.spc_ret_chart_data");
                sqlVo.appendSqlAndAddParam("SET chart_comment = ?, input_timestamp = ?", comment, spcRetChartDataEntity.getInputTimestamp());
                sqlVo.appendSql("WHERE 1=1");
                sqlVo.appendSqlAndAddParam("AND col_typ_fk = ?", colTyp);
                sqlVo.appendSqlAndAddParam("AND grp_no_fk = ?", grpNo);
                sqlVo.appendSqlAndAddParam("AND chart_no_fk = ?", chartNo);
                sqlVo.appendSqlAndAddParam("AND chart_typ_fk = ?", chartTyp);
                sqlVo.appendSqlAndAddParam("AND input_time_d = ?", inputTime);
                int update_cnt = mySql_QueryRunner_Update(sqlVo);
            }
        } catch (Exception e) {
            log.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            outTrx.setRtn_code("0000003");
            outTrx.setRtn_mesg(e.getMessage());
            return outTrx;
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return outTrx;
    }


    /** Chart图隐藏选择点 ***/
    @Transactional
    public XpspcchartO hidePoint(XpspcchartI inTrx){
        XpspcchartO outTrx = new XpspcchartO();

        String colTyp = inTrx.getCol_typ_fk();
        String grpNo = inTrx.getGrp_no_fk();
        String chartNo = inTrx.getChart_no_fk();
        String chartTyp = inTrx.getChart_typ_fk();
        String inputTime = inTrx.getInput_time_d();
        String evtUsr = inTrx.getEvt_usr();

        StringBuffer hql = new StringBuffer("From SpcRetChartDataEntity Where colTypFk = '" + colTyp +"'");
        hql.append(" and grpNoFk = '" + grpNo + "' and chartNoFk = '" + chartNo +"'");
        hql.append(" and chartTypFk = '" + chartTyp + "' and inputTimeD = '" + inputTime +"'");
        List<SpcRetChartDataEntity> spcRetChartDataEntityList = dao.find(SpcRetChartDataEntity.class, hql.toString());

        if (CollectionUtils.isEmpty(spcRetChartDataEntityList)) throw new RuntimeException("找不到SPC中对应的点，错误的输入");

        try {
            for (SpcRetChartDataEntity spcRetChartDataEntity : spcRetChartDataEntityList) {
                //hql方式
                //SpcRetChartDataEntity spcRetChartDataEntity = spcRetChartDataEntityList.get(0);
                //spcRetChartDataEntity.setExdFlg("N");
                //spcRetChartDataEntity.setEvtUsr(evtUsr);
                //dao.update(spcRetChartDataEntity);

                //sql方式
                SqlVo sqlVo = new SqlVo("UPDATE spc_data.spc_ret_chart_data");
                sqlVo.appendSqlAndAddParam("SET exd_flg = 'N', evt_usr = ?, input_timestamp = ?", evtUsr, spcRetChartDataEntity.getInputTimestamp());
                sqlVo.appendSql("WHERE 1=1");
                sqlVo.appendSqlAndAddParam("AND col_typ_fk = ?", colTyp);
                sqlVo.appendSqlAndAddParam("AND grp_no_fk = ?", grpNo);
                sqlVo.appendSqlAndAddParam("AND chart_no_fk = ?", chartNo);
                sqlVo.appendSqlAndAddParam("AND chart_typ_fk = ?", chartTyp);
                sqlVo.appendSqlAndAddParam("AND input_time_d = ?", inputTime);
                int update_cnt = mySql_QueryRunner_Update(sqlVo);
            }
        } catch (Exception e) {
            log.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            outTrx.setRtn_code("0000003");
            outTrx.setRtn_mesg(e.getMessage());
            return outTrx;
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return outTrx;
    }



}
